#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive

if [[ $1 == "" ]];then
   TD_DATE=`date -d '1 days ago' "+%Y-%m-%d"`
else
   TD_DATE=$1
fi

${HIVE_HOME} -S -e "
--todo dim_customer
--拉链导入T+1
--重建临时拉链表
DROP TABLE if EXISTS zx_dwd.dim_customer_tmp;
CREATE TABLE zx_dwd.dim_customer_tmp
(
    id                       STRING,
    customer_relationship_id STRING COMMENT '当前意向id ',
    create_date_time         STRING COMMENT '创建时间',
    update_date_time         STRING COMMENT '最后更新时间',
    deleted                  STRING COMMENT '是否被删除（禁用）',
    name                     STRING COMMENT '姓名',
    idcard                   STRING COMMENT '身份证号',
    birth_year               STRING COMMENT '出生年份',
    gender                   STRING COMMENT '性别',
    phone                    STRING COMMENT '手机号',
    wechat                   STRING COMMENT '微信',
    qq                       STRING COMMENT 'qq号',
    email                    STRING COMMENT '邮箱',
    area                     STRING COMMENT '所在区域',
    leave_school_date        STRING COMMENT '离校时间',
    graduation_date          STRING COMMENT '毕业时间',
    bxg_student_id           STRING COMMENT '博学谷学员ID，可能未关联到，不存在',
    creator                  STRING COMMENT '创建人ID ',
    origin_type              STRING COMMENT '数据来源',
    origin_channel           STRING COMMENT '来源渠道',
    tenant                   STRING,
    md_id                    STRING COMMENT '中台id',

    end_date                 STRING COMMENT '拉链结束日期'
) COMMENT '客户静态信息表'
    PARTITIONED BY (start_date STRING)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
    STORED AS ORC
    TBLPROPERTIES ('orc.compress' = 'SNAPPY');

--导入到临时拉链表
INSERT OVERWRITE TABLE zx_dwd.dim_customer_tmp PARTITION (start_date)
SELECT *
FROM (
         --新增数据
         SELECT id,
                customer_relationship_id,
                create_date_time,
                update_date_time,
                deleted,
                name,
                idcard,
                birth_year,
                gender,
                phone,
                wechat,
                qq,
                email,
                area,
                leave_school_date,
                graduation_date,
                bxg_student_id,
                creator,
                origin_type,
                origin_channel,
                tenant,
                md_id,
                '9999-99-99' end_date,
                '${TD_DATE}' start_date
         FROM zx_ods.customer
         WHERE dt = '${TD_DATE}'
         UNION ALL
         --历史数据
         SELECT c.id,
                customer_relationship_id,
                create_date_time,
                update_date_time,
                deleted,
                name,
                idcard,
                birth_year,
                gender,
                phone,
                wechat,
                qq,
                email,
                area,
                leave_school_date,
                graduation_date,
                bxg_student_id,
                creator,
                origin_type,
                origin_channel,
                tenant,
                md_id,
                IF(up.id IS NULL OR c.end_date < '9999-99-99', c.end_date, DATE_ADD('2022-05-18', -1)) end_date,
                c.start_date
         FROM zx_dwd.dim_customer c
                  LEFT JOIN (SELECT id
                             FROM zx_ods.customer
                             WHERE dt = '${TD_DATE}') up ON c.id = up.id)
ORDER BY id, start_date;

--临时拉链表确认无误后，覆盖拉链表
INSERT OVERWRITE TABLE zx_dwd.dim_customer PARTITION (start_date)
SELECT *
FROM zx_dwd.dim_customer_tmp;
"